use sqlx::{query, query_as, PgExecutor, PgPool, Postgres, QueryBuilder, Result};

use crate::{filter, model, short_url_with_seed, utils::id};

pub async fn is_exists<'a>(e: impl PgExecutor<'a>, url: &str) -> Result<bool> {
    let q = query_as("SELECT COUNT(*) FROM urls WHERE url=$1").bind(url);
    let count: (i64,) = q.fetch_one(e).await?;
    Ok(count.0 > 0)
}

pub async fn insert<'a>(e: impl PgExecutor<'a>, m: &model::url::Url) -> Result<String> {
    let id = id::new();

    let mut q = QueryBuilder::new(
        r#"INSERT INTO urls ("id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired","dateline")"#,
    );

    q.push_values(&[m], |mut b, u| {
        b.push_bind(&id)
            .push_bind(&u.user_id)
            .push_bind(&u.origin)
            .push_bind(&u.url)
            .push_bind(&u.hit)
            .push_bind(&u.has_password)
            .push_bind(&u.password)
            .push_bind(&u.has_expired)
            .push_bind(&u.expired)
            .push_bind(&u.dateline);
    });

    q.build().execute(e).await?;

    Ok(id)
}

pub async fn update<'a>(
    e: impl PgExecutor<'a>,
    m: &model::url::Url,
    skip_update_password: bool,
) -> Result<u64> {
    let mut q = QueryBuilder::new(r#"UPDATE urls SET "#);
    q.push(r#" "has_password"= "#)
        .push_bind(&m.has_password)
        .push(r#", "has_expired"= "#)
        .push_bind(&m.has_expired)
        .push(r#", "expired"= "#)
        .push_bind(&m.expired);

    if !skip_update_password {
        q.push(r#", "password"= "#).push_bind(&m.password);
    }
    q.push(r#" WHERE "id"= "#).push_bind(&m.id);

    let aff = q.build().execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn update_password<'a>(e: impl PgExecutor<'a>, id: &str, password: &str) -> Result<u64> {
    let has_password = !password.is_empty();
    let q = query(r#"UPDATE urls SET has_password=$1, "password"=$2 WHERE id=$3"#)
        .bind(&has_password)
        .bind(password)
        .bind(id);

    let aff = q.execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn update_expired<'a>(
    e: impl PgExecutor<'a>,
    id: &str,
    expired: Option<chrono::DateTime<chrono::Local>>,
) -> Result<u64> {
    let has_expired = expired.is_some();
    let expired = match expired {
        Some(v) => v,
        None => chrono::Local::now(),
    };

    let q = query(r#"UPDATE urls SET has_password=$1, "password"=$2 WHERE id=$3"#)
        .bind(&has_expired)
        .bind(&expired)
        .bind(id);

    let aff = q.execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn increment_hit<'a>(e: impl PgExecutor<'a>, url: &str) -> Result<u64> {
    let q = query(r#"UPDATE urls SET "hit" = "hit" + 1 WHERE "url"=$1"#).bind(url);

    let aff = q.execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn find<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::url::FindBy<'a>,
) -> Result<Option<model::url::Url>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired","dateline" FROM "urls" WHERE 1=1"#,
    );

    match f {
        &filter::url::FindBy::ID(id) => q.push(" AND id=").push_bind(id),
        &filter::url::FindBy::URL(url) => q.push(" AND url=").push_bind(url),
    };

    q.build_query_as().fetch_optional(e).await
}

pub async fn list_data<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::url::List<'a>,
) -> Result<Vec<model::url::Url>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "id", "user_id", "origin", "url", "hit", "has_password", "password", "has_expired", "expired","dateline" FROM "urls" WHERE 1=1"#,
    );

    build_list_query(&mut q, f);

    let order = match f.order {
        Some(v) => v,
        None => "id DESC",
    };
    q.push(" ORDER BY ").push_bind(order);

    q.push(" LIMIT ")
        .push_bind(f.pagination.page_size())
        .push(" OFFSET ")
        .push_bind(f.pagination.offset());

    q.build_query_as().fetch_all(e).await
}

pub async fn list_count<'a>(e: impl PgExecutor<'a>, f: &filter::url::List<'a>) -> Result<u32> {
    let mut q = QueryBuilder::new(r#"SELECT COUNT(*) FROM "urls" WHERE 1=1"#);

    build_list_query(&mut q, f);

    let c: (i64,) = q.build_query_as().fetch_one(e).await?;

    Ok(c.0 as u32)
}

fn build_list_query<'a>(q: &mut QueryBuilder<Postgres>, f: &filter::url::List<'a>) {
    if let Some(v) = f.url {
        let param = format!("%{v}%");
        q.push(" AND url ILIKE ").push_bind(param);
    }

    if let Some(v) = f.origin {
        let param = format!("%{v}%");
        q.push(" AND origin ILIKE ").push_bind(param);
    }

    if let Some(v) = f.has_password {
        q.push(" AND has_password=").push_bind(v);
    }

    if let Some(v) = f.has_expired {
        q.push(" AND has_expired=").push_bind(v);
    }

    if let Some(v) = f.user_id {
        q.push(" AND user_id=").push_bind(v.to_string());
    }

    if let Some(v) = &f.expired_range {
        q.push(" AND (expired BETTEN ")
            .push_bind(v.start)
            .push(" AND ")
            .push_bind(v.end)
            .push(")");
    }
}

pub async fn del<'a>(e: impl PgExecutor<'a>, id: &str, user_id: Option<&str>) -> Result<u64> {
    match user_id {
        Some(v) => Ok(query("DELETE FROM urls WHERE id=$1 AND user_id=$2")
            .bind(id)
            .bind(v)
            .execute(e)
            .await?
            .rows_affected()),
        None => super::del(e, "urls", id).await,
    }
}

pub async fn grab_insert(
    p: &PgPool,
    m: model::url::Url,
    max_seed: Option<u32>,
) -> Result<Option<model::url::Url>> {
    let max_seed = match max_seed {
        Some(v) => v,
        None => 30,
    };

    for seed in 0..max_seed {
        let url = short_url_with_seed(&m.origin, &m.user_id, seed);
        if !is_exists(p, &url).await? {
            let u = model::url::Url { url, ..m };
            let id = insert(p, &u).await?;
            return Ok(Some(model::url::Url { id, ..u }));
        }
    }

    Ok(None)
}
